package com.wangc.servlet.shouzhi;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import com.wangc.tools.DB_tool;

public class ShouzhiDB {
	//插入一条收支记录
	public static boolean insert(String type,String amount, String detail, HttpServletRequest request){
		Connection conn = DB_tool.getCon();
		boolean flag = false;
		String sql = "";
		Calendar ca = Calendar.getInstance();
		String date = ca.get(Calendar.YEAR)+"-"+(ca.get(Calendar.MONTH)+1)+"-"+ca.get(Calendar.DATE);
		try {
			sql = "insert into shouzhi(type,amount,detail,date,user_id) value (?,?,?,?,?)";
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, type);
			pst.setString(2, amount);
			pst.setString(3, detail);
			pst.setString(4, date);
			pst.setObject(5, request.getSession().getAttribute("user_id"));
			int re = pst.executeUpdate();
			if(re==1){
				flag = true;
			}
			pst.close();
			conn.close();
		} catch (SQLException e) { e.printStackTrace(); }
		return flag;
	}
	
	//查找所有收支记录
	public static List<HashMap> sel(){
		Connection conn = DB_tool.getCon();
		boolean flag = false;
		String sql = "";
		List<HashMap> list = new ArrayList<HashMap>();
		try {
			sql = "select id,type,amount,detail,date,user_id from shouzhi";
			PreparedStatement pst = conn.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				HashMap hm = new HashMap();
				hm.put("id", rs.getInt(1));
				hm.put("type", rs.getString(2));
				hm.put("amount", rs.getString(3));
				hm.put("detail", rs.getString(4));
				hm.put("date", (rs.getObject(5)).toString().substring(0, 10));
				hm.put("user_id", rs.getString(6));
				list.add(hm);
			}
		pst.close();
		conn.close();	
		} catch (Exception e) { e.printStackTrace(); }
		return list;
	}
	
	//按条件查找收支记录
	public static List<HashMap> sea(HttpServletRequest request){
		Connection conn = DB_tool.getCon();
		boolean flag = false;
		String sql = "";
		List<HashMap> list = new ArrayList<HashMap>();
		try {
			String type = request.getParameter("type");
			String user_id = request.getParameter("user_id");
			String month = request.getParameter("month");
			if("".equals(type) && "".equals(user_id) && "".equals(month)){
				list = sel();
			}else{
				sql = "select id,type,amount,detail,date,user_id from shouzhi where type like '%"+type+
																					"' and user_id like '%"+user_id+
																					"' and date like '"+month+"%'";
				System.out.println("====>>sql:"+sql);
				PreparedStatement pst = conn.prepareStatement(sql);
				ResultSet rs = pst.executeQuery();
				while(rs.next()){
					HashMap hm = new HashMap();
					hm.put("id", rs.getInt(1));
					hm.put("type", rs.getString(2));
					hm.put("amount", rs.getString(3));
					hm.put("detail", rs.getString(4));
					hm.put("date", (rs.getObject(5)).toString().substring(0, 10));
					hm.put("user_id", rs.getString(6));
					list.add(hm);
				}
				pst.close();
			}
			
		conn.close();	
		} catch (Exception e) { e.printStackTrace(); }
		return list;
	}
	
	public static List<HashMap> selMonth(){
		Connection conn = DB_tool.getCon();
		String sql = "select date from shouzhi";
		Set set = new HashSet();
		PreparedStatement pst = null;
		try {
			pst = conn.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				set.add(rs.getObject(1).toString().substring(0, 7));
			}
			pst.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		List<HashMap> list = new ArrayList<HashMap>(set);
		return list;
		
	}
	
}
